SQL Server: Common Query Tuning Problems and Solutions – Part 1

There are a wide variety of common performance problems that you will encounter when query tuning. Part 1 of this two-part course begins by showing how to validate query execution assumptions around statistics and cardinality in order to ensure you're troubleshooting the true root cause of a query performance issue. You'll then learn about common areas where bad assumptions can occur that negatively impact overall query performance, and about common problems and solutions related to being imprecise when querying a database. The course is very demo-centric, with lots of practical tips and tricks you'll be able to apply in your environment. This course is perfect for developers, DBAs, and anyone responsible for query performance tuning on SQL Server, from SQL Server 2005 onward.
SQL Server: Common Query Tuning Problems and Solutions – Part 1 Course Introduction 00:02:53
Course Introduction 01:08
Course Scope 00:40
Course Structure 01:04
Validating Assumptions 00:47:00
Module Introduction 00:53
Downstream Impact of Bad Assumptions 01:16
Cardinality Estimation and Predicates 01:01
Selectivity 00:43
Query Cardinality Questions 00:54
Cardinality Estimate Inputs 00:39
Statistics 00:41
Demo: DBCC SHOW_STATISTICS STAT_HEADER 03:21
Demo: DBCC SHOW_STATISTICS DENSITY_VECTOR 01:46
Demo: DBCC SHOW_STATISTICS HISTOGRAM 03:19
Demo: Histogram Direct Step Hit 02:17
Demo: Scaling the Estimate 03:36
Demo: Histogram Intra-Step Hit 02:58
Demo: Density Vector With an Unknown Runtime Value 03:54
Demo: Parameter Sniffed Value Estimation 03:45
Demo: Distinct Value Estimation 01:42
Impact to Query Execution and Performance 00:41
Identifying Bad Assumptions - 1 00:46
Demo: Viewing Estimated Vs. Actual Row Counts 03:52
Identifying Bad Assumptions - 2 00:42
Demo: sys.dm_exec_query_profiles 01:45
Demo: Root-level Skews via sys.dm_exec_query_stats 04:50
What Variance is Problematic? 00:55
Module Summary 00:33
SQL Server: Common Query Tuning Problems and Solutions – Part 1 Troubleshooting Bad Assumptions 01:15:15
Module Introduction 00:42
Model Assumptions 01:22
Demo: Jagged Distributions 05:32
Demo: Correlated Predicates 05:20
Demo: Correlated Predicates in SQL Server 2014 06:03
Demo: Correlated Join Predicates, Independent Filter Predicates 04:20
Demo: Searching for Off-Histogram Values 03:19
Demo: Searching for Off-Histogram Values in SQL Server 2014 03:37
Troubleshooting Questions - 1 01:15
Demo: Missing Statistics 03:13
Demo: Selectivity Guesses 03:54
Demo: Stale Statistics 04:24
Demo: Inadequate Sampling 05:43
Demo: Parameter Sensitivity 05:31
Demo: Table Variable Usage 02:24
Demo: MSTVF Usage - Part 1 03:32
Demo: MSTVF Usage - Part 2 02:34
Demo: MSTVF Usage - Part 3 01:29
Troubleshooting Questions - 2 01:41
Demo: Data Type Conversions 04:15
Demo: Buried Predicates - Part 1 02:16
Demo: Buried Predicates - Part 2 02:11
Module Summary 00:26
SQL Server: Common Query Tuning Problems and Solutions – Part 1 Tuning Imprecision Problems 00:26:12
Module Introduction 00:55
Demo: SELECT * 03:26
Demo: Unnecessary Sorts 02:41
Demo: Needless DISTINCT 03:01
Demo: UNION Vs. UNION ALL 02:38
Demo: Leading Wildcards 03:27
Demo: Hidden Cartesian Products 01:55
Demo: Data Type Conversion Issues 03:41
Demo: Redundant Logic and Overlapping Object References 03:33
Module and Course Summary 00:50

Course Introduction	00:02:53
Course Introduction	01:08
Course Scope	00:40
Course Structure	01:04
Validating Assumptions	00:47:00
Module Introduction	00:53
Downstream Impact of Bad Assumptions	01:16
Cardinality Estimation and Predicates	01:01
Selectivity	00:43
Query Cardinality Questions	00:54
Cardinality Estimate Inputs	00:39
Statistics	00:41
Demo: DBCC SHOW_STATISTICS STAT_HEADER	03:21
Demo: DBCC SHOW_STATISTICS DENSITY_VECTOR	01:46
Demo: DBCC SHOW_STATISTICS HISTOGRAM	03:19
Demo: Histogram Direct Step Hit	02:17
Demo: Scaling the Estimate	03:36
Demo: Histogram Intra-Step Hit	02:58
Demo: Density Vector With an Unknown Runtime Value	03:54
Demo: Parameter Sniffed Value Estimation	03:45
Demo: Distinct Value Estimation	01:42
Impact to Query Execution and Performance	00:41
Identifying Bad Assumptions - 1	00:46
Demo: Viewing Estimated Vs. Actual Row Counts	03:52
Identifying Bad Assumptions - 2	00:42
Demo: sys.dm_exec_query_profiles	01:45
Demo: Root-level Skews via sys.dm_exec_query_stats	04:50
What Variance is Problematic?	00:55
Module Summary	00:33
Troubleshooting Bad Assumptions	01:15:15
Module Introduction	00:42
Model Assumptions	01:22
Demo: Jagged Distributions	05:32
Demo: Correlated Predicates	05:20
Demo: Correlated Predicates in SQL Server 2014	06:03
Demo: Correlated Join Predicates, Independent Filter Predicates	04:20
Demo: Searching for Off-Histogram Values	03:19
Demo: Searching for Off-Histogram Values in SQL Server 2014	03:37
Troubleshooting Questions - 1	01:15
Demo: Missing Statistics	03:13
Demo: Selectivity Guesses	03:54
Demo: Stale Statistics	04:24
Demo: Inadequate Sampling	05:43
Demo: Parameter Sensitivity	05:31
Demo: Table Variable Usage	02:24
Demo: MSTVF Usage - Part 1	03:32
Demo: MSTVF Usage - Part 2	02:34
Demo: MSTVF Usage - Part 3	01:29
Troubleshooting Questions - 2	01:41
Demo: Data Type Conversions	04:15
Demo: Buried Predicates - Part 1	02:16
Demo: Buried Predicates - Part 2	02:11
Module Summary	00:26
Tuning Imprecision Problems	00:26:12
Module Introduction	00:55
Demo: SELECT *	03:26
Demo: Unnecessary Sorts	02:41
Demo: Needless DISTINCT	03:01
Demo: UNION Vs. UNION ALL	02:38
Demo: Leading Wildcards	03:27
Demo: Hidden Cartesian Products	01:55
Demo: Data Type Conversion Issues	03:41
Demo: Redundant Logic and Overlapping Object References	03:33
Module and Course Summary	00:50